﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'P_BrowseOperationExecution')
	BEGIN
		DROP  Procedure  [esf_sso].P_BrowseOperationExecution
	END

GO

CREATE Procedure [esf_sso].P_BrowseOperationExecution
	@pCustomOperationName VARCHAR (502) = NULL
	,@pUserAlias VARCHAR (22) = NULL
	,@pExecutionDateTimeStart DATETIME = NULL
	,@pExecutionDateTimeEnd DATETIME = NULL
AS

	SELECT
		X.*
		,COUNT (X.CustomOperationName) calOperationCount
	FROM
	(
		SELECT
			OE.CustomOperationName
			,YEAR (OE.ExecutionDateTime) calYear
			,MONTH (OE.ExecutionDateTime) calMonth
			,VU.Usr_Per_Ent_Name
		FROM
			esf_sso.OperationExecution OE
		INNER JOIN
			esf_sso.VUser VU ON VU.Usr_Alias = OE.UserAlias
		WHERE
			(@pCustomOperationName IS NULL OR CustomOperationName LIKE @pCustomOperationName)
			AND (@pUserAlias IS NULL OR UserAlias LIKE @pUserAlias)
			AND (@pExecutionDateTimeStart IS NULL OR ExecutionDateTime >= @pExecutionDateTimeStart)
			AND (@pExecutionDateTimeEnd IS NULL OR ExecutionDateTime <= @pExecutionDateTimeEnd)
	) X
	GROUP BY
		X.CustomOperationName
		,X.calYear
		,X.calMonth
		,X.Usr_Per_Ent_Name